package com.sds.shop.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;


import com.sds.fw.dao.AbDao;
import com.sds.shop.vo.MemVO;

public class MemDAO extends AbDao {
	private static MemDAO dao = new MemDAO();

	public static MemDAO getInstance() {
		return dao;
	}

	public MemVO getCustomer(String customerId) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = connect();

			StringBuffer query = new StringBuffer();
			query.append("SELECT mem_id, mem_nm, mem_cd, pwd, pwd_hint, pwd_hint_ans, ssn, birth_dt, ");
			query.append("        male_flg, tel_no, cell_no, addr, email, email_rcv_flg, mem_reg_dtm ");
			query.append("FROM tb_mem ");
			query.append("WHERE mem_id = ? ");

			ps = conn.prepareStatement(query.toString());
			ps.setString(1, customerId);
			rs = ps.executeQuery();
			while (rs.next()) {
				
				MemVO vo = new MemVO();

				vo.setMemId(rs.getString(1));
				vo.setMemNm(rs.getString(2));
				vo.setMemCd(rs.getString(3));
				vo.setPwd(rs.getString(4));
				vo.setPwdHint(rs.getString(5));
				vo.setPwdHintAns(rs.getString(6));
				vo.setSsn(rs.getString(7));
				vo.setBirthDt(rs.getString(8));
				vo.setMaleFlg(rs.getString(9));
				vo.setTelNo(rs.getString(10));
				vo.setCellNo(rs.getString(11));
				vo.setAddr(rs.getString(12));
				vo.setEmail(rs.getString(13));
				vo.setEmailRcvFlg(rs.getString(14));
				vo.setMemRegDtm(rs.getString(15));

				return vo;
			}
		} catch (Exception ex) {
			System.out.println("getCustomer exception : " + ex);

		}
		close(conn, ps, rs);
		return null;
	} // getCustomer

	public MemVO getCustomer(String memId, String pwd) {

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = connect();

			StringBuffer query = new StringBuffer();
			query.append("SELECT mem_id, mem_nm, mem_cd, pwd, pwd_hint, pwd_hint_ans, ssn, birth_dt, ");
			query.append("        male_flg, tel_no, cell_no, addr, email, email_rcv_flg, mem_reg_dtm ");
			query.append("FROM tb_mem ");
			query.append("WHERE mem_id = ? ");
			query.append("AND   pwd = ? ");

			ps = conn.prepareStatement(query.toString());
			ps.setString(1, memId);
			ps.setString(2, pwd);
			rs = ps.executeQuery();
			while (rs.next()) {
				MemVO vo = new MemVO();

				vo.setMemId(rs.getString(1));
				vo.setMemNm(rs.getString(2));
				vo.setMemCd(rs.getString(3));
				vo.setPwd(rs.getString(4));
				vo.setPwdHint(rs.getString(5));
				vo.setPwdHintAns(rs.getString(6));
				vo.setSsn(rs.getString(7));
				vo.setBirthDt(rs.getString(8));
				vo.setMaleFlg(rs.getString(9));
				vo.setTelNo(rs.getString(10));
				vo.setCellNo(rs.getString(11));
				vo.setAddr(rs.getString(12));
				vo.setEmail(rs.getString(13));
				vo.setEmailRcvFlg(rs.getString(14));
				vo.setMemRegDtm(rs.getString(15));

				return vo;
			}
		} catch (Exception ex) {
			System.out.println("getCustomer exception : " + ex);

		}
		close(conn, ps, rs);
		return null;
	} // getCustomer

	public Vector<MemVO> list(MemVO vo) {
		Connection conn = null;
		Vector<MemVO> memberList = new Vector<MemVO>();

		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:xe", "shop", "shop");
			Statement stmt = conn.createStatement();

			String id = vo.getMemId();
			String cd = vo.getMemCd();

			String checkPw = null;

			ResultSet loginRs = stmt
					.executeQuery("select pwd from tb_mem where mem_id='" + id
							+ "'");
			while (loginRs.next()) {
				checkPw = loginRs.getString("pwd");
			}

			if (cd.equals("USR")) {
				ResultSet listRs = stmt
						.executeQuery("select mem_id, mem_cd from tb_mem");
				while (listRs.next()) {
					MemVO tempVO = new MemVO();
					tempVO.setMemId(listRs.getString("mem_id"));
					memberList.add(tempVO);
				}
				listRs.close();
				stmt.close();
				conn.close();

			} else {
				memberList = null;
			}

			return memberList;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return memberList;
		} finally {
			try {
				if (!conn.isClosed()) {
					conn.close();
				}
			} catch (SQLException se) {

			}
		}
	}
}
